﻿using System;
using System.Data;
using System.Text;
using CSharpHelpers.Encrypts;
using MySql.Data.MySqlClient;

namespace CSharpHelpers.DBAccess
{
    public abstract class MySqlUtil
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private static readonly string defaultConn = System.Configuration.ConfigurationManager.ConnectionStrings["AutoMemberCard.Default.ConnectionString"].ConnectionString;

        public static MySqlCommand cmd = new MySqlCommand();
        private static string BuildConnStr(string databaseName)
        {
            string connStr = defaultConn;
            if (!String.IsNullOrEmpty(databaseName))
            {
                if (databaseName == DBConst.DB_DEFAULT)
                {
                    connStr = GetNewConnStr(defaultConn);
                }
            }
            else
            {
                connStr = GetNewConnStr(connStr);
            }

            return connStr;
        }

        /// <summary>
        /// 获取解密后的数据库连接字符串
        /// </summary>
        /// <param name="oldCon"></param>
        /// <returns></returns>
        private static string GetNewConnStr(string oldCon)
        {
            int passwordIndex = 0;
            string oldPassword = string.Empty;
            string[] array = oldCon.Split(';');

            for (int i = 0; i < array.Length; i++)
            {
                if (array[i].IndexOf("password=") > 0)
                {
                    passwordIndex = i;
                    string[] arrayPassword = array[i].Split('=');
                    oldPassword = arrayPassword[1];
                    break;
                }
            }

            string newPasword = AESEncryptHelper.AESDecrypt(oldPassword);
            array[passwordIndex] = string.Format("password={0}", newPasword.Trim());

            StringBuilder sb = new StringBuilder();
            foreach (var s in array)
            {
                sb.Append(s);
                sb.Append(";");
            }

            return sb.ToString();
        }

        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        public static int ExecuteNonQuery(string cmdText, MySqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(cmdText, cmdParms, String.Empty);
        }

        public static int ExecuteNonQuery(string cmdText, MySqlParameter[] cmdParms, string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return val;
            }
        }

        public static bool ExecuteNonQuery(CommandType commandType, string[] commandText, params MySqlParameter[][] commandParameters)
        {
            return ExecuteNonQuery(String.Empty, commandType, commandText, commandParameters);
        }

        public static bool ExecuteNonQuery(string databaseName, CommandType commandType, string[] commandText, params MySqlParameter[][] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            bool bSucess = true;

            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;

                using (MySqlTransaction transaction = conn.BeginTransaction())
                {
                    int result;
                    for (int i = 0; i < commandText.Length; i++)
                    {
                        result = 0;
                        try
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = commandText[i];
                            if (commandParameters[i] != null)
                            {
                                foreach (MySqlParameter parm in commandParameters[i])
                                    cmd.Parameters.Add(parm);
                            }

                            result = cmd.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            break;
                        }

                        if (result <= 0)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            break;
                        }
                    }

                    if (bSucess)
                    {
                        transaction.Commit();
                    }
                }

            }

            return bSucess;
        }

        public static object ExecuteScalar(string cmdText, MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static object ExecuteScalar(string cmdText, MySqlParameter[] cmdParms, string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static DataSet GetDataSet(string cmdText, MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataSet GetDataSet(string cmdText, MySqlParameter[] cmdParms, string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataTable GetDataTable(string cmdText, MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static DataTable GetDataTable(string cmdText, MySqlParameter[] cmdParms, string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static MySqlDataReader GetDataReader(string cmdText, MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(BuildConnStr(""));
            try
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception e)
            {
                //SystemLog.Error(e);
                conn.Close();
                throw;
            }
        }

        public static MySqlDataReader GetDataReader(string cmdText, MySqlParameter[] cmdParms, string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(BuildConnStr(""));
            try
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception e)
            {
                //SystemLog.Error(e);
                conn.Close();
                throw;
            }
        }

        public static int ExecuteNonQueryByConn(string cmdText, MySqlParameter[] cmdParms, MySqlConnection ownConn, MySqlTransaction ownTran)
        {
            PrepareCommand(cmd, ownConn, @cmdText, cmdParms);
            if (ownTran != null)
            {
                cmd.Transaction = ownTran;
            }

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        /// <summary>
        /// 数据库是否已连接
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static bool IsConnect(string databaseName)
        {
            MySqlCommand cmd = new MySqlCommand();
            bool bConnected = true;
            using (MySqlConnection conn = new MySqlConnection(BuildConnStr("")))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                }
                catch (Exception e)
                {
                    //SystemLog.Error("連接數據庫異常: " + e);
                    bConnected = false;
                }
            }
            return bConnected;
        }

        #region by Bell  注意 裏面的方法都不關閉連接需要在調用以後 關閉
        public static MySqlConnection BuildConnection(string databaseName)
        {
            var connectionStr = BuildConnStr(databaseName);
            var conn = new MySqlConnection(connectionStr);
            return conn;
        }

        public static int ExecuteNonScalarSqlTran(string cmdText, MySqlParameter[] cmdParms, MySqlConnection conn, MySqlTransaction sqlTran)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            var cmd = new MySqlCommand(cmdText, conn, sqlTran);

            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
                //foreach (MySqlParameter parm in cmdParms)
                //    cmd.Parameters.Add(parm);
            }

            int val = Convert.ToInt32((cmd.ExecuteScalar()));
            cmd.Parameters.Clear();
            //conn.Close();
            return val;
        }

        public static int ExecuteNonQuerySqlTran(string cmdText, MySqlParameter[] cmdParms, MySqlConnection conn, MySqlTransaction sqlTran)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            var cmd = new MySqlCommand(cmdText, conn, sqlTran);

            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
                //foreach (MySqlParameter parm in cmdParms)
                //    cmd.Parameters.Add(parm);
            }

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            //conn.Close();
            return val;
        }

        public static MySqlDataReader GetDataReaderSqlTran(string cmdText, MySqlParameter[] cmdParms, MySqlConnection conn, MySqlTransaction sqlTran)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                var cmd = new MySqlCommand(cmdText, conn, sqlTran);
                if (cmdParms != null)
                {
                    cmd.Parameters.AddRange(cmdParms);
                    //foreach (MySqlParameter parm in cmdParms)
                    //    cmd.Parameters.Add(parm);
                }

                MySqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception e)
            {
                //SystemLog.Error(e);
                throw;
            }
        }

        #endregion

        /// <summary>
        /// 重载,返回事物执行失败原因
        /// _PeterHao
        /// _2016-7-4
        /// </summary>
        /// <param name="errMsg"></param>
        /// <param name="connectionStr"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(out string errMsg, string connectionStr, CommandType commandType, string[] commandText, params MySqlParameter[][] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            bool bSucess = true;
            errMsg = string.Empty;

            using (MySqlConnection conn = new MySqlConnection(connectionStr))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;

                using (MySqlTransaction transaction = conn.BeginTransaction())
                {
                    int result;
                    for (int i = 0; i < commandText.Length; i++)
                    {
                        result = 0;
                        try
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = commandText[i];
                            if (commandParameters[i] != null)
                            {
                                foreach (MySqlParameter parm in commandParameters[i])
                                    cmd.Parameters.Add(parm);
                            }

                            result = cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            errMsg = ex.Message;
                            break;
                        }

                        if (result <= 0)
                        {
                            transaction.Rollback();
                            bSucess = false;
                            errMsg = "受影响的行数为0";
                            break;
                        }
                    }

                    if (bSucess)
                    {
                        transaction.Commit();
                    }
                }

            }

            return bSucess;
        }
    }
}
